package org.ehe.business.workstation.controller;

import cn.dev33.satoken.annotation.SaCheckPermission;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.ehe.business.workstation.domain.Workstation;
import org.ehe.business.workstation.mapper.WorkstationMapper;
import org.ehe.common.satoken.utils.LoginHelper;
import org.ehe.erp.domain.product.bo.ErpProductUnitBo;
import org.ehe.erp.domain.product.vo.ErpMaterialVo;
import org.ehe.erp.domain.product.vo.ErpProductUnitVo;
import org.ehe.erp.service.product.IErpProductUnitService;
import org.ehe.erp.service.product.MaterialService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;

/**
 * 非标件模板下载控制器
 *
 * @author zhangnn
 * @date 2025-10-23
 */
@Slf4j
@RestController
@RequiredArgsConstructor
@RequestMapping("/workstation/template")
public class NonStandardPartTemplateController {

    private final WorkstationMapper workstationMapper;
    private final MaterialService materialService;
    private final IErpProductUnitService unitService;

    /**
     * 下载非标件导入模板
     */
    @SaCheckPermission("business:workstation:query")
    @GetMapping("/downloadNonStandard")
    public void downloadTemplate(HttpServletResponse response) {
        Workbook workbook = null;
        try {
            // 加载模板文件
            InputStream tpl = getClass().getResourceAsStream("/excel/template.xlsm");
            workbook = new XSSFWorkbook(tpl);

            // 删除默认的Sheet
            int idx = workbook.getSheetIndex("Sheet1");
            if (idx != -1) {
                workbook.removeSheetAt(idx);
            }

            // 删除BOM信息表(如果存在)
            idx = workbook.getSheetIndex("BOM信息");
            if (idx != -1) {
                workbook.removeSheetAt(idx);
            }
            idx = workbook.getSheetIndex("物料信息");
            if (idx != -1) {
                workbook.removeSheetAt(idx);
            }

            // 创建非标件主工作表
            Sheet mainSheet = workbook.createSheet("非标件信息");

            // 创建隐藏的数据工作表
            Sheet dataSheet = workbook.createSheet("Data");
            workbook.setSheetHidden(workbook.getSheetIndex(dataSheet), false);

            // 设置主工作表表头
            createMainSheetHeader(mainSheet);

            // 获取数据库数据
            List<Workstation> workstations = workstationMapper.selectList(null);
            List<ErpMaterialVo> materials = materialService.getAllEnabledMaterials(LoginHelper.getTenantId());
            ErpProductUnitBo bo = new ErpProductUnitBo();
            List<ErpProductUnitVo> units = unitService.queryList(bo);

            // 在数据源工作表中填充数据
            int[] dataPositions = fillDataSheet(dataSheet, workstations, materials, units);

            // 保护Data工作表,设置为只读
            protectDataSheet(dataSheet);

            // 设置数据验证(下拉选项)
            setDataValidation(workbook, mainSheet, dataPositions);

            // 设置响应头
            setResponseHeaders(response, "非标件导入模板");

            // 输出到响应流
            try (ServletOutputStream outputStream = response.getOutputStream()) {
                workbook.write(outputStream);
                outputStream.flush();
            }

        } catch (Exception e) {
            log.error("生成非标件Excel模板失败", e);
            if (!response.isCommitted()) {
                response.reset();
                response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
                try {
                    response.getWriter().write("生成Excel模板失败: " + e.getMessage());
                } catch (IOException ioException) {
                    log.error("写入错误响应失败", ioException);
                }
            }
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    log.error("关闭workbook失败", e);
                }
            }
        }
    }

    /**
     * 创建主工作表表头
     */
    private void createMainSheetHeader(Sheet sheet) {
        Row headerRow = sheet.createRow(0);

        // 创建表头样式
        CellStyle headerStyle = sheet.getWorkbook().createCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);

        Font headerFont = sheet.getWorkbook().createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);

        // 设置表头
        String[] headers = {
                "工位编号*", "设备名称", "物料名称*", "规格型号*", "材料*", "单位*", "备注"
        };

        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
            sheet.setColumnWidth(i, 4000);
        }

        // 创建说明行
        Row descRow = sheet.createRow(1);
        Cell descCell = descRow.createCell(0);

        // 创建富文本字符串
        Workbook workbook = sheet.getWorkbook();
        Font redFont = workbook.createFont();
        redFont.setColor(Font.COLOR_RED);

        RichTextString richText = new XSSFRichTextString("请在下方填写非标件信息,带*为必填项(勿删此行)");

        // 设置"勿删此行"为红色
        String targetText = "勿删此行";
        int startIndex = richText.getString().indexOf(targetText);
        if (startIndex != -1) {
            int endIndex = startIndex + targetText.length();
            richText.applyFont(startIndex, endIndex, redFont);
        }

        descCell.setCellValue(richText);

        CellStyle descStyle = sheet.getWorkbook().createCellStyle();
        Font descFont = sheet.getWorkbook().createFont();
        descFont.setColor(IndexedColors.BLUE.getIndex());
        descStyle.setFont(descFont);
        descCell.setCellStyle(descStyle);

        // 合并说明单元格
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headers.length - 1));
    }

    /**
     * 在数据源工作表中填充数据
     */
    private int[] fillDataSheet(Sheet dataSheet,
            List<Workstation> workstations,
            List<ErpMaterialVo> materials,
            List<ErpProductUnitVo> units) {

        Row headerRow = dataSheet.getRow(0);
        if (headerRow == null)
            headerRow = dataSheet.createRow(0);

        // 写入工位数据(code-名称组合)
        int workstationCol = 0;
        headerRow.createCell(workstationCol).setCellValue("工位");
        int r = 1;
        for (Workstation ws : workstations) {
            Row dataRow = dataSheet.getRow(r);
            if (dataRow == null)
                dataRow = dataSheet.createRow(r);
            dataRow.createCell(workstationCol).setCellValue(ws.getCode() + "-" + ws.getName());
            r++;
        }
        int maxRow = r - 1;

        // 写入材料数据
        int materialCol = 1;
        headerRow.createCell(materialCol).setCellValue("材料");
        r = 1;
        for (ErpMaterialVo m : materials) {
            Row dataRow = dataSheet.getRow(r);
            if (dataRow == null)
                dataRow = dataSheet.createRow(r);
            dataRow.createCell(materialCol).setCellValue(m.getMaterialName());
            r++;
        }
        maxRow = Math.max(maxRow, r - 1);

        // 写入单位数据
        int unitCol = 2;
        headerRow.createCell(unitCol).setCellValue("单位");
        r = 1;
        for (ErpProductUnitVo u : units) {
            Row dataRow = dataSheet.getRow(r);
            if (dataRow == null)
                dataRow = dataSheet.createRow(r);
            dataRow.createCell(unitCol).setCellValue(u.getName());
            r++;
        }
        maxRow = Math.max(maxRow, r - 1);

        return new int[] { maxRow, workstationCol, materialCol, unitCol };
    }

    /**
     * 设置数据验证,实现下拉选项
     */
    private void setDataValidation(Workbook workbook, Sheet mainSheet, int[] arr) {
        DataValidationHelper helper = mainSheet.getDataValidationHelper();
        Sheet dataSheet = workbook.getSheet("Data");
        int maxDataRow = arr[0] + 1;
        int workstationCol = arr[1];
        int materialCol = arr[2];
        int unitCol = arr[3];

        // 设置"工位编号"下拉列表(A列,index 0,从第2行开始)
        CellRangeAddressList workstationRange = new CellRangeAddressList(2, 1000, 0, 0);
        String workstationColLetter = CellReference.convertNumToColString(workstationCol);
        String workstationFormula = String.format("Data!$%s$2:$%s$%d",
                workstationColLetter, workstationColLetter, maxDataRow);
        DataValidation workstationValidation = helper.createValidation(
                helper.createFormulaListConstraint(workstationFormula), workstationRange);
        workstationValidation.setSuppressDropDownArrow(true);
        workstationValidation.setShowErrorBox(true);
        mainSheet.addValidationData(workstationValidation);

        // 设置"材料"下拉列表(E列,index 4,从第2行开始)
        CellRangeAddressList materialRange = new CellRangeAddressList(2, 1000, 4, 4);
        String materialColLetter = CellReference.convertNumToColString(materialCol);
        String materialFormula = String.format("Data!$%s$2:$%s$%d",
                materialColLetter, materialColLetter, maxDataRow);
        DataValidation materialValidation = helper.createValidation(
                helper.createFormulaListConstraint(materialFormula), materialRange);
        materialValidation.setSuppressDropDownArrow(true);
        materialValidation.setShowErrorBox(true);
        mainSheet.addValidationData(materialValidation);

        // 设置"单位"下拉列表(F列,index 5,从第2行开始)
        CellRangeAddressList unitRange = new CellRangeAddressList(2, 1000, 5, 5);
        String unitColLetter = CellReference.convertNumToColString(unitCol);
        String unitFormula = String.format("Data!$%s$2:$%s$%d",
                unitColLetter, unitColLetter, maxDataRow);
        DataValidation unitValidation = helper.createValidation(
                helper.createFormulaListConstraint(unitFormula), unitRange);
        unitValidation.setSuppressDropDownArrow(true);
        unitValidation.setShowErrorBox(true);
        mainSheet.addValidationData(unitValidation);
    }

    /**
     * 保护Data工作表,禁止编辑其中的数据
     */
    private void protectDataSheet(Sheet dataSheet) {
        String password = "eiahe@123";

        if (dataSheet instanceof XSSFSheet) {
            XSSFSheet xssfSheet = (XSSFSheet) dataSheet;
            org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetProtection protection = xssfSheet
                    .getCTWorksheet().getSheetProtection();
            if (protection == null) {
                protection = xssfSheet.getCTWorksheet().addNewSheetProtection();
            }
            protection.setSelectLockedCells(true);
            protection.setSelectUnlockedCells(true);
            protection.setFormatCells(false);
            protection.setFormatColumns(false);
            protection.setFormatRows(false);
            protection.setInsertColumns(false);
            protection.setInsertRows(false);
            protection.setDeleteColumns(false);
            protection.setDeleteRows(false);
            xssfSheet.protectSheet(password);
        } else {
            dataSheet.protectSheet(password);
        }

        // 锁定所有单元格
        CellStyle lockedStyle = dataSheet.getWorkbook().createCellStyle();
        lockedStyle.setLocked(true);

        for (int rowNum = 0; rowNum <= dataSheet.getLastRowNum(); rowNum++) {
            Row row = dataSheet.getRow(rowNum);
            if (row == null)
                continue;
            for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                Cell cell = row.getCell(cellNum);
                if (cell == null) {
                    cell = row.createCell(cellNum);
                }
                cell.setCellStyle(lockedStyle);
            }
        }
    }

    /**
     * 设置HTTP响应头
     */
    private void setResponseHeaders(HttpServletResponse response, String filename) {
        response.setContentType("application/vnd.ms-excel.sheet.macroEnabled.12");
        response.setCharacterEncoding("UTF-8");

        String finalFilename = (filename == null || filename.isBlank() ? "非标件导入模板"
                : filename.replaceAll("\\.\\w+$", "")) + ".xlsm";

        try {
            String encodedFilename = URLEncoder.encode(finalFilename, "UTF-8").replace("+", "%20");
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + encodedFilename + "\"; filename*=UTF-8''" + encodedFilename);
        } catch (UnsupportedEncodingException e) {
            response.setHeader("Content-Disposition", "attachment; filename=\"template.xlsm\"");
        }

        response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Expires", "0");
    }
}
